package DAOImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import DAO.IBooksDAO;
import POJO.Books;
import Util.DBUtil;

public class BooksDAOIplm implements IBooksDAO {
	

	public int addBook(Books book) {
		String sql = "insert into Books(id,name,pub_date,author,price,is_new,publisher) values(seq01.nextval,?,?,?,?,?,?)";
		Connection con =null;
		PreparedStatement pst = null;
		int index = 0;
		
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setDate(2, new java.sql.Date(book.getPubDate().getTime()));
			pst.setString(3, book.getAutor());
			pst.setDouble(4, book.getPrice());
			pst.setBoolean(5, book.isNew());
			pst.setString(6, book.getPublisher());
			index = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(con, pst, null);
		}
		return index;
	}

	public int deleteBook(Integer id) {
		String sql = "delete books where id=?";
		Connection con =null;
		PreparedStatement pst = null;
		int index = 0;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			index = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(con, pst, null);
		}
		return index;
	}

	public int updateBook(Books book) {
		String sql = "update books set name=?,pub_date=?,author=?,price=?,is_new=?,publisher=? where id=?";
		Connection con =null;
		PreparedStatement pst = null;
		int index = 0;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setDate(2, new java.sql.Date(book.getPubDate().getTime()));
			pst.setString(3, book.getAutor());
			pst.setDouble(4, book.getPrice());
			pst.setBoolean(5, book.isNew());
			pst.setString(6, book.getPublisher());
			pst.setInt(7, book.getId());
			index = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, pst, null);
		}
		return index;
	}

	public Books findById(Integer id) {
		String sql ="select * from books where id=?";
		Connection con =null;
		PreparedStatement pst = null;
		Books book = new Books();
		ResultSet  rs = null;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if(rs.next()){
				book.setId(id);
				book.setName(rs.getString("name"));
				book.setPubDate(new Date(rs.getDate("pub_date").getTime()));
				book.setAutor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setNew(rs.getBoolean("is_new"));
				book.setPublisher(rs.getString("publisher"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, pst, rs);
		}
		return book;
	}

	public List<Books> findAll() {
		List<Books> list = new ArrayList<Books>();
		String sql ="select * from books";
		Connection con =null;
		PreparedStatement pst = null;
		ResultSet  rs = null;
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()){
				Books book = new Books();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setPubDate(new Date(rs.getDate("pub_date").getTime()));
				book.setAutor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setNew(rs.getBoolean("is_new"));
				book.setPublisher(rs.getString("publisher"));
				list.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, pst, rs);
		}
		return list;
	}

	public int addBooks(List<Books> books) {
		String sql = "insert into Books(id,name,pub_date,author,price,is_new,publisher) values(seq01.nextval,?,?,?,?,?,?)";
		int index = 0;
		Connection con = null;
		PreparedStatement pst = null;
		con = DBUtil.getConnection();
		try {
			con.setAutoCommit(false);
			for (Books b : books) {
				pst = con.prepareStatement(sql);
				pst.setString(1, b.getName());
				pst.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
				pst.setString(3, b.getAutor());
				pst.setDouble(4, b.getPrice());
				pst.setBoolean(5, b.isNew());
				pst.setString(6, b.getPublisher());
				pst.addBatch();
			}
			index = pst.executeBatch().length;
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return index;
	}

}
